using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using Dimok.Data;
using Dimok.Data.Database;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.ApplicationBlocks.ExceptionManagement;
using Dimok;
using Dimok.Exceptions;
using Dimok.Aspx.Manager;
using Data.DataSources;
using Data.LINQ;

namespace Data{
	public class SQL:SQLHelperEx{
		public static string ConnectionString { get { return new UOX().db; } }
		public static string ConnectionString_ADO { get { return new UOX().dbADO; } }

		public class TestUpdate {
			public static void CreditInfo(int BorrowerID) {
				new MotoBank().t_CreditInfos.Update(r => r.BorrowerID == BorrowerID, new Dictionary<string, object>() { { "UpdateTest", DateTime.Now } });
			}
		}
		public class Note {
			public static void UpdateStatus(object sender) {
				ListControl sel = sender as DropDownList;
				int noteID = Lib.MakeInt(sel.Attributes["NoteID"]);
				NotesDataSource dsNotes = new NotesDataSource();
				Dimok.OrderedDictionaryEx keys = new Dimok.OrderedDictionaryEx("ID", noteID);
				dsNotes.UpdateField(keys, "StatusID", sel.SelectedValue, false);
			}
		}
		#region Overloads
		public static string Xml(string sql, params object[] parameters) {
			DataSet ds = ExecuteDataset(sql, parameters);
			int t = 1;
			foreach (DataTable dt in ds.Tables)
				dt.TableName = "T" + (t++);
			return XmlInnerText(ds);
		}
		public static DataTable ExecuteDataset(DataTable customTable, string sql, params object[] parameters) {
			return ExecuteDatasetEx(ConnectionString, customTable, sql, parameters);
		}
		public static DataSet ExecuteDataset(int timeOut, string sql, params object[] parameters) {
			return ExecuteDatasetEx(ConnectionString, timeOut, sql, parameters);
		}
		public static DataSet ExecuteDataset(string sql, params object[] parameters) {
			return ExecuteDatasetEx(ConnectionString, 60 * 1, sql, parameters);
		}

		public static void ExecuteNonQuery(string sql, params object[] parameters) {
			ExecuteNonQuery(-1, sql, parameters);
		}
		public static void ExecuteNonQuery(int commandTimeout, string sql, params object[] parameters) {
			ExecuteNonQueryEx(ConnectionString, commandTimeout, sql, parameters);
		}

		public static object ExecuteScalar(string sql, params object[] parameters) {
			return SQLHelperEx.ExecuteScalarEx(ConnectionString, sql, parameters);
		}
		public static Dimok.ArrayListEx ExecuteScalarArray(string sql, params object[] parameters) {
			return SQLHelperEx.ExecuteScalarArray(ConnectionString, sql, parameters);
		}
		public static string ExecuteXML(string sql, params SqlParameter[] parameters) {
			return ExecuteXML(ConnectionString, CommandType.Text, sql, parameters);
		}
		public static int ExecuteSP(string sql, params object[] parameters) {
			return (int)ExecSP(ConnectionString, sql, parameters);
		}
		public static int ExecuteSP(int timeOut, string sql, params object[] parameters) {
			return (int)ExecSP(ConnectionString, timeOut, sql, parameters);
		}
		public static string ADOToString(string sql, params object[] parameters) {
			return SQLHelperEx.ADOToString(ConnectionString_ADO, sql, parameters);
		}
		public static DataTable UpdateTable(IDictionary fields, string sql, params object[] parameters) {
			return UpdateDataTable(fields, ConnectionString, 0, sql, parameters);
		}
		public static DataTable UpdateTable(IDictionary fields, int commandTimeout, string sql, params object[] parameters) {
			return UpdateDataTable(fields, ConnectionString, commandTimeout, sql, parameters);
		}
		#endregion


		#region StateFlow
		public class StateFlow {
			public static DataView StateFlowTree(string tableName, object principal_id) {
				return ExecuteDatasetEx(ConnectionString, 60 * 30, "s_ShowSFKids", tableName, principal_id).Tables[0].DefaultView;
			}
			public static void StateFlowAddPath(int stateFromID, int stateToID, object principal_id) {
				ExecuteNonQueryEx(ConnectionString,"INSERT INTO t_StateFlow(StateFromID,StateToID) VALUES(@StateFromID,@StateToID)", stateFromID, stateToID);
				if ((principal_id + "") != "") StateFlowAddGroup(stateFromID, stateToID, principal_id);
			}
			public static void StateFlowAddGroup(int stateFromID, int stateToID, object principal_id) {
				try {
					ExecuteNonQueryEx(ConnectionString, "INSERT INTO t_StateFlow_Groups(StateFromID,StateToID,principal_id) VALUES(@StateFromID,@StateToID,@principal_id)",
						stateFromID, stateToID, principal_id);
				} catch (Exception exc) {
					exc.Data.Add("principal_id", principal_id);
					throw exc;
				}
			}
			public static void StateFlowRemPath(int stateFromID, int stateToID) {
				ExecuteNonQueryEx(ConnectionString, "DELETE FROM t_StateFlow WHERE StateFromID = @StateFromID AND StateToID = @StateToID", stateFromID, stateToID);
			}
			public static void StateFlowRemGroup(int stateFromID, int stateToID, object principal_id) {
				ExecuteNonQueryEx(ConnectionString, "DELETE FROM t_StateFlow_Groups WHERE StateFromID = @StateFromID AND StateToID = @StateToID AND principal_id = @principal_id",
					 stateFromID, stateToID, principal_id);
			}
		}
		#endregion


		#region Interviews
		public const string ReferenceInterviewTable = "ts_ReferenceInterview";
		public static string ReferenceInterview(){
			return SQL.ADOToString(UOX.dbRawAdo,"SELECT * FROM "+ReferenceInterviewTable);
		}
		public const string BorrowerInterviewTable = "ts_BorrowerInterview";
		public static string BorrowerInterview(){
			return SQL.ADOToString(UOX.dbRawAdo,"SELECT * FROM "+BorrowerInterviewTable);
		}
		public const string InterviewDoneByTable = "ts_InterviewDoneBy";
		public static string InterviewDoneBy(){
			return SQL.ADOToString(UOX.dbRawAdo,"SELECT * FROM "+InterviewDoneByTable);
		}
		#endregion References

		#region Applicant Info
		public const string v_applInfo = "SELECT * FROM vx_ApplInfo WHERE ";

		public static string V_ApplInfo(int applID){return v_applInfo+UOX.ApplicantFilter(applID);}
		public static DataSet ApplInfo(string conn,int applID){
			string sql = V_ApplInfo(applID);
			try{
				return ExecuteDataset(conn,CommandType.Text,sql);
			}catch(Exception exc){
				throw new ASPException(exc, "SQL", sql);
			}
		}
/*
		public static void ApplInfo(string conn,int applID,string fieldName,string fieldValue){
			string update = 
				"UPDATE vx_Dealers_ApplInfo SET ~F~ = @Value WHERE "+Reg.ApplicationFilter.Replace("~~ID~~",applID.ToString());
			update = update.Replace("~F~",fieldName);
			UpdateField(conn,"SELECT * FROM vx_Dealers_ApplInfo WHERE "+,PrepSQLParams(update,fieldValue,applID));
		}
		public static void ApplInfoCo(string conn,int coApplID,string fieldName,string fieldValue){
			string update = 
				"UPDATE t_Applicants SET ~F~ = @Value WHERE ApplicantID = @ApplID";
			update = update.Replace("~F~",fieldName);
			ExecuteNonQuery(conn,update,PrepSQLParams(update,fieldValue,coApplID));
		}
		*/
		#endregion Applicant Info

		#region Employment Info
		public const string NonEmploymentIncomeSQL = "SELECT DISTINCT NonEmploymentIncomeDesc t,NonEmploymentIncomeDesc v FROM t_Borrower";
		public static DataSet EmplInfo(string conn,int applID,int borrPos,int jobNumber,int jobPos){
			return ExecuteDatasetEx(conn,"SELECT * FROM Employment(@LoanID,@BorrPos,@JobNum,@JobPos)",applID,borrPos,jobNumber,jobPos);
		}
		public static void EmplInfo(string conn,int borrID,int jobNum,int jobPos,string fieldName,object fieldValue){
			//ExecuteNonQueryEx(conn,"s_Employment_Add",applID,borrPos,jobNumber,jobPos);
			HashTableEx ht = new HashTableEx("BorrowerID",borrID,"JobNumber",jobNum,"Pos",jobPos,fieldName,fieldValue);
			SQLHelperEx.UpdateDataTable(ht,conn,"SELECT * FROM t_Employment WHERE BorrowerID=@BID AND JobNumber=@JobNum AND Pos=@Pos",borrID,jobNum,jobPos);
		}
		#endregion

		#region Address Info
		public readonly static string v_addrInfo = "SELECT a.* FROM t_Address a INNER JOIN v_Borrower_0 b ON a.BorrowerID = b.ID WHERE b."+Dimok.Config.db.ApplID+"=@ApplID AND Pos=@Index";

		public static string V_AddrInfo(int applID,int index){return SQL.PrepSQL(v_addrInfo,applID,index);}

		public static DataSet AddrInfo(string conn,int applID,int index){
			return ExecuteDatasetEx(conn,v_addrInfo,applID,index);
		}
		public static int AddrInfo(string conn,int applID,int index,string fieldName,object fieldValue){
			int addrID = (int)ExecSP(conn,"s_Address_Add",applID,index);
			UpdateField(conn,V_AddrInfo(applID,index),fieldName,fieldValue);
			return addrID;
		}
		#endregion

		#region Zip Info
		public static string GetCitiesByZip(string connADO,string zipCode){
			return SQL.ADOToString(connADO,"SELECT CityName t,CityName v,StateCode st FROM Zips_USPS WHERE Zip = ?",zipCode);
		}
		public static string GetStateByZip(string conn,string zipCode){
			return (string)SQL.ExecuteScalarEx(conn,"SELECT CityName t,StateCode st FROM Zips_USPS WHERE Zip = @Zip",zipCode);
		}
		public static void GetCitiesByZip(string conn,string zipCode,int applID,int index){
				SQL.ExecSP(conn,"s_SetCityStateByZip",applID,index,zipCode);
		}
		#endregion Zip Info

		#region Dealers
		public const string DEALERS_TABLE = "vsec_Dealer";
		public const string Dealers_V = "SELECT ID,LEFT(Name,25)+ISNULL('('+UsixID+')','') Name FROM "+DEALERS_TABLE+" ORDER BY NAME";
		#endregion

		#region Trackers
		public const string SQL_TRACKING_DEVICES = "SELECT * FROM v_TrackerInfo";

		#endregion

		#region VIN
		public static DataSet GetVINsToGo(string conn, string filter){
			return SQL.ExecuteDatasetEx(conn,"SELECT * FROM v_VINsToGo WHERE VIN LIKE @Filter ORDER BY VIN",filter);
		}
		#endregion
	}
}
